import sys

from peak.binding.components import Component
from protocols import advise
from interfaces import ISQLParser

from pyparsing import Literal, CaselessLiteral, Word, Upcase, delimitedList, \
    Combine, Group, Forward 
from pyparsing import Optional, oneOf, ZeroOrMore, restOfLine
from pyparsing import alphas, nums, alphanums, quotedString
from pyparsing import ParseException

class SQLParserError(Exception):
    pass

class SQLParser(Component):

    '''
    Doctests:

    >>> import parser
    >>> sql = parser.SQLParser('test string')
    >>> sql.setupGrammar()
    '''

    advise(instancesProvide=[ISQLParser])

    def __init__(self, sql):

        self.sql = sql
        self.statement = None

        # SQL language definition useful for all classes
        # 'ident' is the definition for legal words in the language
        self.ident             = Word( alphas, alphanums + "_$" ).setName("identifier")
        self.column_name       = Upcase( delimitedList( self.ident, ".", combine=True ) )
        self.column_name_list  = Group( delimitedList( self.column_name ) )
        self.table_name        = Upcase( delimitedList( self.ident, ".", combine=True ) )
        self.table_name_list   = Group( delimitedList( self.table_name ) )

        self.where_expression = Forward()
        self.and_ = CaselessLiteral("and")
        self.or_ = CaselessLiteral("or")
        self.in_ = CaselessLiteral("in")

        E = CaselessLiteral("E")
        self.binop = oneOf("= != < > >= <= eq ne lt le gt ge like", caseless=True)
        arith_sign = Word("+-",exact=1)
        real_num = Combine( Optional(arith_sign) + ( Word( nums ) + "." + Optional( Word(nums) )  |
            ( "." + Word(nums) ) ) + Optional( E + Optional(arith_sign) + Word(nums) ) )
        int_num = Combine( Optional(arith_sign) + Word( nums ) + Optional( E + Optional("+") + Word(nums) ) )

        # need to add support for alg expressions
        self.column_rval = real_num | int_num | quotedString | self.column_name 


    def setupGrammar(self):
        pass

    def parseGrammar(self):
        try:
            return self.statement.parseString(self.sql)
        except ParseException, e:
            raise SQLParserError("Possible error in SQL or grammar definitions: %s." % e.msg)

    def getTokens(self):
        self.setupGrammar()
        return self.parseGrammar()
            

class Create(SQLParser):

    '''
    Doctests:

    Create a database:
    >>> import parser
    >>> sql = parser.Create("CREATE DATABASE '/desired/path/to/db' MYDB")
    >>> sql.setupGrammar()
    >>> tokens = sql.parseGrammar()
    >>> tokens.id  
    (['MYDB'], {})
    >>> tokens.path
    (["'/DESIRED/PATH/TO/DB'"], {})
    >>> tokens.metatype
    (['DATABASE'], {})

    A shorter way:
    >>> sql = parser.Create("CREATE DATABASE '/desired/path/to/db' MYDB")
    >>> tokens = sql.getTokens()
    >>> tokens.id
    (['MYDB'], {})
    >>> tokens.path
    (["'/DESIRED/PATH/TO/DB'"], {})
    >>> tokens.metatype
    (['DATABASE'], {})

    '''

    def setupGrammar(self):
        
        # define SQL tokens
        create_stmt        = Forward()
        create_token       = CaselessLiteral( "create" )

        # SQL language definition
        # 'metatypeName' the first chunk of SQL after the verb (create); the part 
        # that says what type to create (in this case, database or table).
        ident              = self.ident
        metatype_name      = Upcase( delimitedList( ident, ".", combine=True ) )
        # 'metatype_name_list' will be a PyParser 'Group', in this case containing the 
        # 'metatype_name'
        metatype_name_list = Group( delimitedList( metatype_name ) )
        id_name            = Upcase( delimitedList( ident, ".", combine=True ) )
        id_name_list       = Group( delimitedList( id_name ) )

        path_ident         =  Word(alphas + alphanums + "/" + "'")
        path_name          =  Upcase( delimitedList( path_ident, "/", combine=True ) )
        path_name_list     = Group( delimitedList( path_name ) )

        # define the grammar of the SQL language definition
        create_stmt        << ( create_token + 
            metatype_name_list.setResultsName("metatype") +
            path_name_list.setResultsName("path") +
            id_name_list.setResultsName("id") )

        self.statement = create_stmt
        self.statement.validate()

class Insert(SQLParser):
    '''
    Doctests:

    >>> import parser

    >>> sql = parser.Select("BAD SELECT")
    >>> tokens = sql.getTokens()
    Traceback (most recent call last):
    File "<stdin>", line 1, in ?
    File "parser.py", line 69, in getTokens
        return self.parseGrammar()
    File "parser.py", line 64, in parseGrammar
        raise SQLParserError("Possible error in SQL or grammar definitions: %s (%s)." % 
    SQLParserError: Possible error in SQL or grammar definitions: Expected 'select'.

    >>> sql = parser.Insert('Insert into sdfsdf(a,b,c) values(aaa,bbb,ccc)')
    >>> tokens = sql.getTokens()
    >>> tokens.tables
    (['SDFSDF'], {})
    >>> tokens.columns
    (['A', 'B', 'C'], {})
    >>> tokens.field_values
    (['AAA', 'BBB', 'CCC'], {})
    

    >>> sql = parser.Insert('Insert into sdfsdf(b) values(bbb)')
    >>> tokens = sql.getTokens()
    >>> tokens.tables
    (['SDFSDF'], {})
    >>> tokens.columns
    (['B'], {})
    >>> tokens.field_values
    (['BBB'], {})
    
    '''
    
    def setupGrammar(self):
        # define SQL tokens
        insert_stmt      = Forward()
        insert_token     = CaselessLiteral( "insert" )
        into_token       = CaselessLiteral( "into" )
        value_token      = CaselessLiteral( "values" )

        ident            = self.ident
        column_name      = self.column_name
        column_name_list = self.column_name_list
        table_name_list  = self.table_name_list
        value_name       = Upcase( delimitedList( ident, ".", combine=True ) )
        value_name_list  = Group( delimitedList( value_name ) )
        column_rval      = self.column_rval
        where_expression = self.where_expression

        where_condition = Group(
            ( column_name + self.binop + column_rval ) |
            ( column_name + self.in_ + "(" + delimitedList( column_rval ) + ")" ) |
            ( column_name + self.in_ + "(" + insert_stmt + ")" ) |
            ( "(" + where_expression + ")" )
        )
        self.where_expression << where_condition + ZeroOrMore( ( self.and_ | self.or_ ) + where_expression ) 

        # define the grammar
        insert_stmt      << ( insert_token + into_token + table_name_list.setResultsName("tables") +
            Optional( "(" + column_name_list.setResultsName("columns") + ")") +
            value_token + "(" + value_name_list.setResultsName("field_values") + ")" +
            Optional( Group( CaselessLiteral("where") + where_expression ), "" ).setResultsName("where") )

        self.statement = insert_stmt
        self.statement.validate()


class Select(SQLParser):

    '''
    Doctests:

    >>> import parser
    >>> sql = parser.Select("SELECT * from XYZZY, ABC")
    >>> tokens = sql.getTokens()
    >>> tokens.columns
    (['*'], {})
    >>> tokens.tables 
    (['XYZZY', 'ABC'], {})
    >>> tokens.where 
    ''

    >>> sql = parser.Select("Select A, B, C from Sys.dual, Table2   ")
    >>> tokens = sql.getTokens()
    >>> tokens.columns
    ([(['A', 'B', 'C'], {})], {})
    >>> tokens.tables
    (['SYS.DUAL', 'TABLE2'], {})
    >>> tokens.where
    ''

    >>> sql = parser.Select("Select A from Sys.dual where a in ('RED','GREEN','BLUE') and b in (10,20,30)")
    >>> tokens = sql.getTokens()
    >>> tokens.columns
    ([(['A'], {})], {})
    >>> tokens.tables
    (['SYS.DUAL'], {})
    >>> tokens.where
    (['where', (['A', 'in', '(', "'RED'", "'GREEN'", "'BLUE'", ')'], {}), 'and', (['B', 'in', '(', '10', '20', '30', ')'], {})], {})

    '''

    def setupGrammar(self):
        # define SQL tokens
        select_stmt = Forward()
        select_token = CaselessLiteral( "select" )
        from_token   = CaselessLiteral( "from" )

        ident            = self.ident
        column_name      = self.column_name
        column_name_list = self.column_name_list
        table_name_list  = self.table_name_list
        column_rval      = self.column_rval
        where_expression = self.where_expression

        where_condition = Group(
            ( column_name + self.binop + column_rval ) |
            ( column_name + self.in_ + "(" + delimitedList( column_rval ) + ")" ) |
            ( column_name + self.in_ + "(" + select_stmt + ")" ) |
            ( "(" + where_expression + ")" )
        )
        self.where_expression << where_condition + ZeroOrMore( ( self.and_ | self.or_ ) + where_expression ) 

        # define the grammar
        select_stmt      << ( select_token + ( '*' | column_name_list ).setResultsName( "columns" ) + 
            from_token + table_name_list.setResultsName( "tables" ) + 
            Optional( Group( CaselessLiteral("where") + where_expression ), "" ).setResultsName("where") )

        self.statement = select_stmt
        self.statement.validate()


def _test():
    import doctest, parser
    return doctest.testmod(parser)

if __name__ == '__main__':
    _test()

    
